package com.whale.cloud.util.jdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.whale.cloud.util.StringUtils;
import com.whale.cloud.util.TimeUtils;
import lombok.extern.log4j.Log4j2;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/** Created by xdl on 2017/5/25. JdbcClient 针对数据库 增删改查 */
@Log4j2
public class JdbcClient {

  private JdbcTemplate jdbcTemplate;

  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

  public void setJdbcTemplate(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  /**
   * 新增
   *
   * @param sql
   * @param params
   * @return 记录ID
   */
  public long insert(String sql, Object[] params) {

    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbcTemplate.update(
        new PreparedStatementCreator() {
          public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (params == null) {
              return ps;
            }
            Object op = null;
            int alength = params.length;
            for (int i = 0; i < alength; ++i) {
              op = params[i];
//              StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, op);
              StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, op==null?op:StringUtils.valueOf(op));
            }
            return ps;
          }
        },
        keyHolder);
    return keyHolder.getKey().intValue();
  }

  /**
   * 更新
   *
   * @param sql
   * @param params
   * @return 影响的行数
   */
  public int update(String sql, Object[] params) {
    log.debug("update sql:" + sql);
    List<Object> list=null;
    if (params != null && params.length > 0) {
      list=new ArrayList<>();
      for (Object o : params) {
        log.debug("value2:" + o);
        list.add(o==null?o:StringUtils.valueOf(o));
      }
    }
    return jdbcTemplate.update(sql, list!=null && list.size()>0?list.toArray():null);
//    return jdbcTemplate.update(sql, params);
  }

  /**
   * 删除
   *
   * @param sql
   * @param params
   * @return 受影响的行数
   */
  public int delete(String sql, Object[] params) {
    return jdbcTemplate.update(sql, params);
  }

  /**
   * 说明：查询，返回的是Json对象
   *
   * @return JSONObject
   */
  public JSONObject getJSONObject(String sql, Object[] params) {
    return getJSONObject(sql, params, false);
  }

  public JSONObject getJSONObject(String sql, Object[] params, final boolean toUpper) {
    log.debug("save sql:" + sql);
    if (params != null && params.length > 0) {
      for (Object o : params) {
        log.debug("value:" + o);
      }
    }
    return jdbcTemplate.query(
        sql,
        params,
        new ResultSetExtractor<JSONObject>() {
          public JSONObject extractData(ResultSet resultSet)
              throws SQLException, DataAccessException {
            ResultSetMetaData rsd = resultSet.getMetaData();
            int clength = rsd.getColumnCount();
            String columnName;
            try {
              if (resultSet.next()) {
                JSONObject jo = new JSONObject();

                for (int i = 0; i < clength; i++) {
                  columnName = rsd.getColumnLabel(i + 1);
                  columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
                  jo.put(columnName, resultSet.getObject(i + 1));
                }
                return jo;
              }
            } catch (Exception e) {
              log.debug("get data error:" + e.getLocalizedMessage());
              e.printStackTrace();
            }
            return null;
          }
        });
  }

  /**
   * 查询
   *
   * @return JSONArray json数组
   */
  public JSONArray getJSONArray(String sql, Object[] params) {
    JSONArray ja = getJSONArray(sql, params, false);
    return ja;
  }

  public JSONArray getJSONArray(String sql, Object[] params, final boolean toUpper) {
    log.debug("get sql:" + sql);
    if (params != null && params.length > 0) {
      for (Object o : params) {
        log.debug("value:" + o);
      }
    }

    return jdbcTemplate.query(
        sql,
        params,
        new ResultSetExtractor<JSONArray>() {
          public JSONArray extractData(ResultSet resultSet)
              throws SQLException, DataAccessException {
            ResultSetMetaData rsd = resultSet.getMetaData();
            int clength = rsd.getColumnCount();
            JSONArray ja = new JSONArray();
            String columnName;
            try {
              while (resultSet.next()) {
                JSONObject jo = new JSONObject();
                for (int i = 0; i < clength; i++) {
                  columnName = rsd.getColumnLabel(i + 1);
                  columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
                  //region 处理时间
                  if("createdate".equalsIgnoreCase(columnName) || "modifydate".equalsIgnoreCase(columnName)){
                    jo.put(columnName,
                            TimeUtils.DATETIME_FORMAT_DATE.format(TimeUtils.DATETIME_FORMAT_DATE.parse(resultSet.getObject(i + 1).toString())));
                  }else {
                    //endregion
                    jo.put(columnName, resultSet.getObject(i + 1));
                  }
                }
                ja.add(jo);
              }
            } catch (Exception e) {
              log.debug("get data error:" + e.getLocalizedMessage());
              e.printStackTrace();
            }
            log.debug("---->SpringJdbcService:" + ja.toString());
            return ja;
          }
        });
  }

  /**
   * 说明：查询记录，例如：查询手机号码是否存在
   *
   * @param sql
   * @param params
   * @return
   */
  public int getCount(String sql, Object[] params) {
    return doQueryOne(sql, params, Integer.class);
  }

  /**
   * 说明：查询一条记录，
   *
   * @param sql
   * @param params
   * @return
   */
  public <T> T doQueryOne(String sql, Object[] params, Class<T> returnTtype) {
    log.debug("save sql:" + sql);
    if (params != null && params.length > 0) {
      for (Object o : params) {
        log.debug("value:" + o);
      }
    }
    T o = null;
    try {
      o = jdbcTemplate.queryForObject(sql, params, returnTtype);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return o;
  }
}
